﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using CRMFramework;
using System.Net;
using Newtonsoft.Json.Linq;


namespace CRMBiz
{
    public static class EmailSettingHandler
    {
        /// <summary>
        /// function to bind email accounts
        /// </summary>
        /// <param name="key">Id</param>
        /// <param name="value">EmailId</param>
        /// <returns>data</returns>
        public static DataTable BindEmailAccounts()
        {
            DataTable dt;
            try
            {
                dt = DBhelper.ExecuteDataset("SELECT EmailId FROM `crm_emailaccount` ORDER BY EmailId").Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// function to get email account data
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns>EmailList</returns>
        public static DataSet GetEmailAccountData(int pageIndex, int pageSize)
        {
            DataSet ds;
            try
            {
                var sbSql = new StringBuilder("select *,(select count(*) from crm_CompanyEmailAccount) as Total from crm_CompanyEmailAccount ORDER BY Company ASC , IsDefault DESC , EmailAccount ASC ");
                sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                ds = DBhelper.ExecuteDataset(sbSql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        /// <summary>
        /// function to add new setting
        /// </summary>
        /// <param name="NoticeDescription"></param>
        /// <returns>New notice</returns>
        public static int AddSettings(string Company, string EmailAccount, int IsDefault)
        {
            bool chk = false;
            string sbSql = string.Empty;
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@Company", Company),
                    DBhelper.SetParameter("@EmailAccount", EmailAccount),
                    DBhelper.SetParameter("@IsDefault", IsDefault),
                };

                sbSql = "Select Company,EmailAccount From crm_CompanyEmailAccount Where Company=@Company and EmailAccount = @EmailAccount";
                DataSet ds = DBhelper.ExecuteDataset(sbSql, para);
                if (ds.Tables[0].Rows.Count > 0)
                    intReturnValue = 0;
                else if (IsDefault == 1)
                {
                    string setSql = "Select IsDefault From crm_CompanyEmailAccount Where Company = @Company";
                    DataSet ds_def = DBhelper.ExecuteDataset(setSql, para);
                    if (ds_def.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < ds_def.Tables[0].Rows.Count; i++)
                        {
                            string chkVal = ds_def.Tables[0].Rows[i]["IsDefault"].ToString();
                            if (chkVal == "1")
                                chk = true;
                        }
                        if (chk == true)
                            intReturnValue = 2;
                        else
                        {
                            sbSql = "insert into crm_CompanyEmailAccount(Company,EmailAccount,IsDefault) values(@Company,@EmailAccount,@IsDefault)";
                            DBhelper.ExecuteNonQuery(sbSql, para);
                            intReturnValue = 1;
                        }
                    }
                    else
                    {
                        sbSql = "insert into crm_CompanyEmailAccount(Company,EmailAccount,IsDefault) values(@Company,@EmailAccount,@IsDefault)";
                        DBhelper.ExecuteNonQuery(sbSql, para);
                        intReturnValue = 1;
                    }
                }
                else
                {
                    sbSql = "insert into crm_CompanyEmailAccount(Company,EmailAccount,IsDefault) values(@Company,@EmailAccount,@IsDefault)";
                    DBhelper.ExecuteNonQuery(sbSql, para);
                    intReturnValue = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }


        /// <summary>
        /// function to Edit setting
        /// </summary>
        /// <param name="NoticeID"></param>
        /// <param name="NoticeDescription"></param>
        /// <returns>intReturnValue</returns>
        public static int EditSettings(int ID, string Company, string EmailAccount, int IsDefault)
        {
            string sbSql = string.Empty;
            bool chk = false;
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ID", ID),
                    DBhelper.SetParameter("@Company", Company),
                    DBhelper.SetParameter("@EmailAccount", EmailAccount),
                    DBhelper.SetParameter("@IsDefault", IsDefault),
                };
                sbSql = "Select Company,EmailAccount From crm_CompanyEmailAccount Where ID <> @ID And  EmailAccount = @EmailAccount and Company=@Company";
                DataSet ds = DBhelper.ExecuteDataset(sbSql, para);
                if (ds.Tables[0].Rows.Count > 0)
                    intReturnValue = 0;
                else if (IsDefault == 1)
                {
                    string setSql = "Select IsDefault From crm_CompanyEmailAccount Where Company = @Company and ID <> @ID";
                    DataSet ds_def = DBhelper.ExecuteDataset(setSql, para);
                    if (ds_def.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < ds_def.Tables[0].Rows.Count; i++)
                        {
                            string chkVal = ds_def.Tables[0].Rows[i]["IsDefault"].ToString();
                            if (chkVal == "1")
                                chk = true;
                        }
                        if (chk == true)
                            intReturnValue = 2;
                        else
                        {
                            sbSql = "update crm_CompanyEmailAccount set Company =@Company,EmailAccount=@EmailAccount,IsDefault=@IsDefault where ID = @ID";
                            DBhelper.ExecuteNonQuery(sbSql, para);
                            intReturnValue = 1;
                        }
                    }
                    else
                    {
                        sbSql = "update crm_CompanyEmailAccount set Company =@Company,EmailAccount=@EmailAccount,IsDefault=@IsDefault where ID = @ID";
                        DBhelper.ExecuteNonQuery(sbSql, para);
                        intReturnValue = 1;
                    }
                }
                else
                {
                    sbSql = "update crm_CompanyEmailAccount set Company =@Company,EmailAccount=@EmailAccount,IsDefault=@IsDefault where ID = @ID";
                    DBhelper.ExecuteNonQuery(sbSql, para);
                    intReturnValue = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        /// <summary>
        /// function to Delete setting
        /// </summary>
        /// <param name="NoticeID"></param>   
        /// <returns>true/false</returns>
        public static bool DeleteSettings(int ID)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ID", ID),
                };
                string sbSql = "delete from crm_CompanyEmailAccount where ID=@ID";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static DataTable getSendingNameforEmail(string emailID)
        {
            DataTable dt = null;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@p_emailID", emailID),
                };
                dt = DBhelper.ExecuteDataset("Select ifnull(SendingName,'') as SendingName ,ifnull(SMTPserver,'') as SMTPserver From crm_emailaccount Where EmailId=@p_emailID", para).Tables[0];

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        }
}
